# coding = utf-8
import time
import pymysql.cursors
import jsonpath as jsonpath
import requests
import os
import sys
import json
from selenium import webdriver
# browser = webdriver.Chrome(executable_path='D:/pythonProject/python_ant/chromedriver_win32/chromedriver.exe')
# # 当测试好能够顺利爬取后，为加快爬取速度可设置无头模式，即不弹出浏览器
# # 添加无头headlesss 1使用chrome headless,2使用PhantomJS
# # 使用 PhantomJS 会警告高不建议使用phantomjs，建议chrome headless
# # chrome_options = webdriver.ChromeOptions()
# # chrome_options.add_argument('--headless')
# # browser = webdriver.Chrome(chrome_options=chrome_options)
# # browser = webdriver.PhantomJS()
# # browser.maximize_window()  # 最大化窗口,可以选择设置
#
# browser.get('http://www.shipxy.com/Ship/Detail?mmsi=215748000')
# time.sleep(10)
# print(browser.page_source)
#
# browser.quit()

def __init__(self):
    self.connection = pymysql.connect(host='ezcook2014.mysql.rds.aliyuncs.com',
                                      user='csguser',
                                      password='csguser@1234',
                                      db='csg_db',
                                      charset='utf8mb4',
                                      cursorclass=pymysql.cursors.DictCursor)

    sql = "truncate table ship_track;"
    self.connection.cursor().execute(sql)  # connection 数据库连接对象 cursor()数据库交互对象
    self.connection.commit()


def test():
    url = "http://www.shipxy.com/Ship/GetVoyage"
    headers = {  'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.146 Safari/537.36',
                 'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
                 'Cookie': 'SERVERID=8dac9e937b8701fba4cb1394cffade3e|1612923733|1612923250',
                 'Keep-Alive': 'timeout=4',
                 'X-Requested-With': 'XMLHttpRequest',
                 'Connection': 'keep-alive'
                 }
    body = { 'btime': '1610331677',
             'etime': '1612923677',
             'mmsi': '215748000'
             }

    # f=open(r'./txt_file/cookie.txt','r')#打开所保存的cookies内容文件
    # cookies={}#初始化cookies字典变量
    # for line in f.read().split(';'):   #按照字符：进行划分读取
    #     #其设置为1就会把字符串拆分成2份
    #     name,value=line.strip().split('=',1)
    #     cookies[name]=value  #为字典cookies添加内容
    r = requests.post(url=url,headers=headers,json=body)
    print(r.text)

def GetTime(stamp):
    format_time = time.strftime("%Y-%m-%d %H:%M:%S",stamp)
    return format_time

def GetTimeStamp(str):
    TimeStamp = int(time.mktime(str))
    return TimeStamp
def ConvertStampToTime(stamp):
    x = time.localtime(stamp)
    return time.strftime('%Y-%m-%d %H:%M:%S', x)

def GetShipBymmsiTest (mmsi,btime,etime):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36'
    }
    session = requests.Session()
    main_url = 'http://www.shipxy.com/'  # 推测对该url发起请求会产生cookie
    session.get(main_url, headers=headers)
    url = 'http://www.shipxy.com/Ship/GetVoyage'
    #使用session机制
    #使用场景:动态变化的cookie
    #session对象:该对象和requests模块用法几乎一致.如果在请求的过程中产生了cookie,如果该请求使用session发起的,则cookie会被自动存储到session中.

    body = { 'btime': btime,#'1610331677',#1610695130
             'etime': etime,#'1612923677',#1613287130
             'mmsi': mmsi
             }
    page_text = session.get(url, headers=headers, json=body).json()
    print(page_text)
    # db = pymysql.connect("ezcook2014.mysql.rds.aliyuncs.com","csguser","csguser@1234","csg_db")
    # cursor = db.cursor()
    # sql_search = 'select * from ship_track where mmsi_number = ' + mmsi + ";"
    # cursor.execute(sql_search)
    # try:
    #     cursor.execute(sql_search)
    #     results = cursor.fetchall()
    #     print(len(results))
    # except:
    #     print("error")

def GetShipBymmsi (mmsi,btime,etime):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36'
    }
    session = requests.Session()
    main_url = 'http://www.shipxy.com/'  # 推测对该url发起请求会产生cookie
    session.get(main_url, headers=headers)
    url = 'http://www.shipxy.com/Ship/GetVoyage'
    #使用session机制
    #使用场景:动态变化的cookie
    #session对象:该对象和requests模块用法几乎一致.如果在请求的过程中产生了cookie,如果该请求使用session发起的,则cookie会被自动存储到session中.

    body = { 'btime': btime,#'1610331677',#1610695130
             'etime': etime,#'1612923677',#1613287130
             'mmsi': mmsi
             }
    page_text = session.get(url, headers=headers, json=body).json()
    print(page_text)
    Port_lists = jsonpath.jsonpath(page_text,"$..Port")
    PortEn_lists = jsonpath.jsonpath(page_text,"$..PortEn")
    ATA_lists = jsonpath.jsonpath(page_text, "$..ATA")
    ATB_lists = jsonpath.jsonpath(page_text, "$..ATB")
    ATD_lists = jsonpath.jsonpath(page_text, "$..ATD")
    SailingTime_lists = jsonpath.jsonpath(page_text, "$..SailingTime")
    Country_CN_lists = jsonpath.jsonpath(page_text, "$..Country_CN")
    Distance_lists = jsonpath.jsonpath(page_text, "$..Distance")
    Speed_lists = jsonpath.jsonpath(page_text, "$..Speed")
    StayInPort_lists = jsonpath.jsonpath(page_text, "$..StayInPort")
    StayInTerminal_lists = jsonpath.jsonpath(page_text, "$..StayInTerminal")
    WaitBoTime_lists = jsonpath.jsonpath(page_text, "$..WaitBoTime")
    PorttimeZone_lists = jsonpath.jsonpath(page_text, "$..PorttimeZone")
    # print(len(PorttimeZone_lists))
    #print(Port_lists,PortEn_lists,ATA_lists)
    # host = 'ezcook2014.mysql.rds.aliyuncs.com',
    # user = 'csguser',
    # password = 'csguser@1234',
    # db = 'csg_db',
    # charset = 'utf8mb4',
    # cursorclass = pymysql.cursors.DictCursor)
    db = pymysql.connect("ezcook2014.mysql.rds.aliyuncs.com","csguser","csguser@1234","csg_db",charset="utf8")
    cursor = db.cursor()
    # cursor.execute("select * from ship;")
    # # record = cursor.fetchone()
    # print("select * from ship is: ", record)
    #获取数组长度作为下标
    sql_search = 'select * from ship_track where mmsi_number = ' + mmsi + ";"
    cursor.execute(sql_search)
    results = cursor.fetchall()
    if (len(results) > 0):
        sql = ("DELETE FROM SHIP_TRACK WHERE mmsi_number = " + mmsi + ";")
        cursor.execute(sql)
        db.commit()
        print("delete success")
    for index in range(0,len(Port_lists)):
        sql = "INSERT INTO SHIP_TRACK(mmsi_number, port_chinese, " + \
              "port_english, port_country, arrive_time, berthing_time, " + \
              "leave_time, stay_time, work_time, wait_time, ship_hour, " + \
              "ship_distance, ship_speed, port_time_zone, begin_time_stamp, end_time_stamp, gmt_create) VALUES " + \
              "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        print(type(sql))
        try:
            print("begin insert")
            # cursor.execute(sql, (mmsi,Port_lists[index],PortEn_lists[index],Country_CN_lists[index],ATA_lists[index],
            #                      ATB_lists[index], ATD_lists[index], StayInPort_lists[index],StayInTerminal_lists[index],
            #                      WaitBoTime_lists[index], SailingTime_lists[index], Distance_lists[index], Speed_lists[index],
            #                      PorttimeZone_lists[index],btime,etime,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())))
            cursor.execute(sql, (mmsi,Port_lists[index],PortEn_lists[index],Country_CN_lists[index],ATA_lists[index],
                                 ATB_lists[index], ATD_lists[index], StayInPort_lists[index],StayInTerminal_lists[index],
                                 WaitBoTime_lists[index], SailingTime_lists[index], Distance_lists[index], Speed_lists[index],
                                 PorttimeZone_lists[index],btime,etime,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())))
            print("insert success")
            db.commit()
            print("commit success")
        except:
            db.rollback() #发生错误时回滚
    #关闭指针对象
    cursor.close()
    #关闭连接对象
    db.close()
#
mmsi = sys.argv[1]
btime = sys.argv[2]
etime = sys.argv[3]
# GetShipBymmsi(mmsi,btime,etime)
# print(ConvertStampToTime(1611021384))
# print(ConvertStampToTime(1613613384))
# print(GetTimeStamp("2021-01-19 09:56:24"))
# 2,592,000
# mmsi = "477250700"
# btime = "1613464314"
# etime = "1614328315"
# GetShipBymmsiTest(mmsi,btime,etime)
GetShipBymmsi(mmsi,btime,etime)
